package com.zyl.edthresholdtraining.mapper;

import com.zyl.edthresholdtraining.entity.UserInfo;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.zyl.edthresholdtraining.vo.UserTestExcelVO;
import com.zyl.edthresholdtraining.vo.UserTrainExcelVO;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * InnoDB free: 48128 kB Mapper 接口
 * </p>
 *
 * @author zhangyulu
 * @since 2020-07-31
 */
public interface UserInfoMapper extends BaseMapper<UserInfo> {

    @Select(" select ui.id userId, ui.name userName, ui.sex userSex, ui.age userAge,tpa.create_time createTime,tpa.test_time testTime " +
            " ,avg(utr.right_rate) rightRate,avg(utr.avg_time) avgTime from t_user_info ui " +
            " INNER JOIN t_time_partition_allow tpa on ui.id = tpa.user_id AND ui.id>10000 " +
            " inner JOIN t_user_test_result utr ON tpa.time_partition_uuid = utr.time_partition_uuid and utr.type=1 " +
            " GROUP BY utr.time_partition_uuid order by ui.id ")
    List<UserTrainExcelVO> getUserTrainExcelVO();

    @Select(" select ui.id userId, ui.name userName, ui.sex userSex, ui.age userAge,tpa.create_time createTime,tpa.test_time testTime,tpa.time_partition timePartition " +
            " ,avg(utr.right_rate) rightRate,avg(utr.avg_time) avgTime from t_user_info ui " +
            " INNER JOIN t_time_partition_allow tpa on ui.id = tpa.user_id AND ui.id>10000 " +
            " inner JOIN t_user_test_result utr ON tpa.time_partition_uuid = utr.time_partition_uuid and utr.type=2 " +
            " GROUP BY utr.time_partition_uuid order by ui.id ")
    List<UserTestExcelVO> getUserTestExcelVO();

}
